﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTFDAILYData : BaseFunction
    {
        
        public JLTFDAILYData()
            : base()
        {

        }
        public JLTFDAILYData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetFDAILY(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "FDAILY";
            string ART = dwABPOS["ART"].ToString().Trim();
            string STAFF = dwABPOS["STAFF"].ToString().Trim();
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            string STARTTIME = dwABPOS["STARTTIME"].ToString().Trim();
            string ENDTIME = dwABPOS["ENDTIME"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT TRIM(A.WBEWART) AS ART,TRIM(A.WBEWFARBE) AS COLOR, "
                    + " A.WBEWBUCHDAT,substr(lpad(wbewbuchuhr,8,'0'),1,2) AS TT,A.WBEWBUCHBEN AS USERID,A.WBEWMPOS AS MACHINE, "
                    + " SUM(NVL(decode(a.wbewlb,'CP',DECODE(B.WBBZENSUR,1,A.WBEWMTR*0.001,0)),0)) AS AMTR, "
                    + " SUM(NVL(decode(a.wbewlb,'CP',DECODE(B.WBBZENSUR,2,A.WBEWMTR*0.001,0)),0)) AS BMTR, "
                    + " SUM(NVL(decode(a.wbewlb,'LB',A.WBEWMTR*0.001,0),0)) AS CMTR,SUM(A.WBEWMTR*0.001) AS TMTR, "
                    + " SUM(NVL(decode(a.wbewlb,'DC', A.WBEWMTR * 0.001, 0),0)) AS DCMTR,"
                    + " SUM(NVL(decode(a.wbewlb, 'HX', A.WBEWMTR * 0.001, 0),0)) AS HXMTR,"
                    + " SUM(NVL(decode(a.wbewlb, 'XD', A.WBEWMTR * 0.001, 0),0)) AS XDMTR,"
                    + " (SUM(NVL(decode(a.wbewlb,'CP',DECODE(B.WBBZENSUR,1,A.WBEWMTR*0.001,0)),0))+SUM(NVL(decode(a.wbewlb,'CP',DECODE(B.WBBZENSUR,2,A.WBEWMTR*0.001,0)),0))+SUM(NVL(decode(a.wbewlb,'LB',A.WBEWMTR*0.001,0),0))+SUM(NVL(decode(a.wbewlb, 'DC', A.WBEWMTR * 0.001, 0),0))+SUM(NVL(decode(a.wbewlb, 'HX', A.WBEWMTR * 0.001, 0),0))+SUM(NVL(decode(a.wbewlb, 'XD', A.WBEWMTR * 0.001, 0),0))) AS TTMTR"
                    + " ,SUM(NVL(DECODE(B.WBBZENSUR,1,DECODE(B.WBBLB,'CP',B.WBBISTKLG,'CW',B.WBBISTKLG,'KO',B.WBBISTKLG,0)*0.001,0),0)) AS AQTY, "
                    + " SUM(NVL(DECODE(B.WBBZENSUR,2,DECODE(B.WBBLB,'CP',B.WBBISTKLG,'CW',B.WBBISTKLG,'KO',B.WBBISTKLG,0)*0.001,0),0)) AS BQTY, "
                    + " SUM(NVL(DECODE(B.WBBLB,'CP',B.WBBISTKLG,'CW',B.WBBISTKLG,'KO',B.WBBISTKLG,0)*0.001,0)) AS QTY "
                    + " FROM WBBEW A ,WBBST B  "
                    + " WHERE A.WBEWMANDANT = B.WBBMANDANT AND A.WBEWRWKRS = B.WBBRWKRS "
                    + "  AND A.WBEWRWLFD = B.WBBRWLFD AND A.WBEWPOS = B.WBBPOS  "
                    + "  AND A.WBEWBS ='13' AND A.WBEWWAKZ = 'F'  "
                    + "  AND (A.WBEWBUCHDAT||substr(lpad(wbewbuchuhr,8,'0'),1,2) >='" + FROMDNDATE +STARTTIME+ "' "
                    + "  AND A.WBEWBUCHDAT||substr(lpad(wbewbuchuhr,8,'0'),1,2) <='" + ENDDNDATE +ENDTIME+ "')  "
                    + "  AND A.WBEWART LIKE '%" + ART + "%' "
                    + "  AND (TRIM(A.WBEWBUCHBEN) = '" + STAFF + "' OR '" + STAFF + "' = 'AAAAA' ) "
                    + "  AND A.WBEWLB IN ('LB','CP','DC','HX','XD') "
                    + " GROUP BY A.WBEWART,A.WBEWFARBE,A.WBEWBUCHDAT,A.WBEWBUCHBEN,A.WBEWMPOS,substr(lpad(wbewbuchuhr,8,'0'),1,2)  "
                    + " ORDER BY 3,4,1,2   "
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName; 
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }       
     
    }
}